﻿// -----------------------------------------------------------------------
// <copyright file="ProfileTypeData.cs" company="VDC-IT" author="LTDUC">
//      Copyright (c) VDC-IT. All rights reserved.
// </copyright>
// -----------------------------------------------------------------------

namespace VDC.VONE.DAL
{
    using System;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.Reflection;
    using Microsoft.ApplicationBlocks.Data;
    using VDC.COMMON;
    using VDC.VONE.ENTITIES;
    using VDC.COMMON.NonPersistents;
    
    /// <summary>
    /// Class access to db ProfileType
    /// </summary>
    public class ProfileTypeData
    {
        /// <summary>
        /// Get ProfileType By Id
        /// </summary>
        /// <param name="profileTypeId">Param profileTypeId</param>
        /// <returns>Return ProfileType</returns>
        public ProfileTypeEN GetProfileTypeById(int profileTypeId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                ProfileTypeEN profileType = null;
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileTypeGetById, profileTypeId);
                while (reader.Read())
                {
                    profileType = new ProfileTypeEN();

                    if (!reader.IsDBNull(0))
                    {
                        profileType.ProfileTypeId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        profileType.WorkflowId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        profileType.DepartmentId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        profileType.ProfileTypeGroupId = reader.GetInt32(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        profileType.ImplementationListId = reader.GetInt32(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        profileType.ResultsImplementationListId = reader.GetInt32(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        profileType.Code = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        profileType.Name = reader.GetString(7);
                    }

                    if (!reader.IsDBNull(8))
                    {
                        profileType.AmountProfile = reader.GetInt32(8);
                    }

                    if (!reader.IsDBNull(9))
                    {
                        profileType.ResolveDays = reader.GetInt32(9);
                    }

                    if (!reader.IsDBNull(10))
                    {
                        profileType.Fees = reader.GetDecimal(10);
                    }
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return profileType;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Get list all ProfileType
        /// </summary>
        /// <returns>Return list all ProfileType</returns>
        public IList<ProfileTypeEN> GetProfileTypes()
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<ProfileTypeEN> lstProfileType = new List<ProfileTypeEN>();
                SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileTypeGetAll);
                while (reader.Read())
                {
                    ProfileTypeEN profileType = new ProfileTypeEN();

                    if (!reader.IsDBNull(0))
                    {
                        profileType.ProfileTypeId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        profileType.WorkflowId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        profileType.DepartmentId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        profileType.ProfileTypeGroupId = reader.GetInt32(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        profileType.ImplementationListId = reader.GetInt32(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        profileType.ResultsImplementationListId = reader.GetInt32(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        profileType.Code = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        profileType.Name = reader.GetString(7);
                    }

                    if (!reader.IsDBNull(8))
                    {
                        profileType.AmountProfile = reader.GetInt32(8);
                    }

                    if (!reader.IsDBNull(9))
                    {
                        profileType.ResolveDays = reader.GetInt32(9);
                    }

                    if (!reader.IsDBNull(10))
                    {
                        profileType.Fees = reader.GetDecimal(10);
                    }

                    lstProfileType.Add(profileType);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstProfileType;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Insert to db ProfileType
        /// </summary>
        /// <param name="profileType">Paarm ProfileType</param>
        /// <returns>Insert ProfileType</returns>
        public int Insert(ProfileTypeEN profileType)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[]
                                {
                                    new SqlParameter(Constants.SqlParamerterName.ProfileType.WorkflowId, profileType.WorkflowId != 0 ? profileType.WorkflowId : (object)DBNull.Value),
                                    new SqlParameter(Constants.SqlParamerterName.ProfileType.DepartmentId, profileType.DepartmentId != 0 ? profileType.DepartmentId : (object)DBNull.Value),
                                    new SqlParameter(Constants.SqlParamerterName.ProfileType.ProfileTypeGroupId, profileType.ProfileTypeGroupId != 0 ? profileType.ProfileTypeGroupId : (object)DBNull.Value),
                                    new SqlParameter(Constants.SqlParamerterName.ProfileType.ImplementationListId, profileType.ImplementationListId != 0 ? profileType.ImplementationListId : (object)DBNull.Value),
                                    new SqlParameter(Constants.SqlParamerterName.ProfileType.ResultsImplementationListId, profileType.ResultsImplementationListId != 0 ? profileType.ResultsImplementationListId : (object)DBNull.Value),
                                    new SqlParameter(Constants.SqlParamerterName.ProfileType.Code, profileType.Code),
                                    new SqlParameter(Constants.SqlParamerterName.ProfileType.Name, profileType.Name),
                                    new SqlParameter(Constants.SqlParamerterName.ProfileType.AmountProfile, profileType.AmountProfile),
                                    new SqlParameter(Constants.SqlParamerterName.ProfileType.ResolveDays, profileType.ResolveDays),
                                    new SqlParameter(Constants.SqlParamerterName.ProfileType.Fees, profileType.Fees)
                                };

                var result = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileTypeInsert, sqlParameters));
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return result;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Update to db ProfileType
        /// </summary>
        /// <param name="profileType">Param ProfileType</param>
        public void Update(ProfileTypeEN profileType)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[]
                                {
                                    new SqlParameter(Constants.SqlParamerterName.ProfileType.ProfileTypeId, profileType.ProfileTypeId),
                                    new SqlParameter(Constants.SqlParamerterName.ProfileType.WorkflowId, profileType.WorkflowId != 0 ? profileType.WorkflowId : (object)DBNull.Value),
                                    new SqlParameter(Constants.SqlParamerterName.ProfileType.DepartmentId, profileType.DepartmentId != 0 ? profileType.DepartmentId : (object)DBNull.Value),
                                    new SqlParameter(Constants.SqlParamerterName.ProfileType.ProfileTypeGroupId, profileType.ProfileTypeGroupId != 0 ? profileType.ProfileTypeGroupId : (object)DBNull.Value),
                                    new SqlParameter(Constants.SqlParamerterName.ProfileType.ImplementationListId, profileType.ImplementationListId != 0 ? profileType.ImplementationListId : (object)DBNull.Value),
                                    new SqlParameter(Constants.SqlParamerterName.ProfileType.ResultsImplementationListId, profileType.ResultsImplementationListId != 0 ? profileType.ResultsImplementationListId : (object)DBNull.Value),
                                    new SqlParameter(Constants.SqlParamerterName.ProfileType.Code, profileType.Code),
                                    new SqlParameter(Constants.SqlParamerterName.ProfileType.Name, profileType.Name),
                                    new SqlParameter(Constants.SqlParamerterName.ProfileType.AmountProfile, profileType.AmountProfile),
                                    new SqlParameter(Constants.SqlParamerterName.ProfileType.ResolveDays, profileType.ResolveDays),
                                    new SqlParameter(Constants.SqlParamerterName.ProfileType.Fees, profileType.Fees)
                                };

                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileTypeUpdate, sqlParameters);
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Delete ProfileType by profileTypeId
        /// </summary>
        /// <param name="profileTypeId">param profileTypeId</param>
        public void Delete(int profileTypeId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[] 
                { 
                    new SqlParameter(Constants.SqlParamerterName.ProfileType.ProfileTypeId, profileTypeId)
                };

                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileTypeDelete, sqlParameters);
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Get List ProfileType By ProfileTypeGroupId
        /// </summary>
        /// <param name="profileTypeGroupId">Param profileTypeGroupId</param>
        /// <returns>Return List ProfileType</returns>
        public IList<ProfileTypeEN> GetProfileTypeByProfileTypeGroupId(int profileTypeGroupId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<ProfileTypeEN> lstProfileType = new List<ProfileTypeEN>();
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileTypeGetByProfileTypeGroupId, profileTypeGroupId);
                while (reader.Read())
                {
                    ProfileTypeEN profileType = new ProfileTypeEN();

                    if (!reader.IsDBNull(0))
                    {
                        profileType.ProfileTypeId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        profileType.WorkflowId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        profileType.DepartmentId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        profileType.ProfileTypeGroupId = reader.GetInt32(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        profileType.ImplementationListId = reader.GetInt32(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        profileType.ResultsImplementationListId = reader.GetInt32(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        profileType.Code = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        profileType.Name = reader.GetString(7);
                    }

                    if (!reader.IsDBNull(8))
                    {
                        profileType.AmountProfile = reader.GetInt32(8);
                    }

                    if (!reader.IsDBNull(9))
                    {
                        profileType.ResolveDays = reader.GetInt32(9);
                    }

                    if (!reader.IsDBNull(10))
                    {
                        profileType.Fees = reader.GetDecimal(10);
                    }

                    lstProfileType.Add(profileType);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstProfileType;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Get List ProfileType By ResultsImplementationListId
        /// </summary>
        /// <param name="resultsImplementationListId">Param resultsImplementationListId</param>
        /// <returns>Return List ProfileType</returns>
        public IList<ProfileTypeEN> GetProfileTypeByResultsImplementationListId(int resultsImplementationListId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<ProfileTypeEN> lstProfileType = new List<ProfileTypeEN>();
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileTypeGetByResultsImplementationListId, resultsImplementationListId);
                while (reader.Read())
                {
                    ProfileTypeEN profileType = new ProfileTypeEN();

                    if (!reader.IsDBNull(0))
                    {
                        profileType.ProfileTypeId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        profileType.WorkflowId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        profileType.DepartmentId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        profileType.ProfileTypeGroupId = reader.GetInt32(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        profileType.ImplementationListId = reader.GetInt32(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        profileType.ResultsImplementationListId = reader.GetInt32(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        profileType.Code = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        profileType.Name = reader.GetString(7);
                    }

                    if (!reader.IsDBNull(8))
                    {
                        profileType.AmountProfile = reader.GetInt32(8);
                    }

                    if (!reader.IsDBNull(9))
                    {
                        profileType.ResolveDays = reader.GetInt32(9);
                    }

                    if (!reader.IsDBNull(10))
                    {
                        profileType.Fees = reader.GetDecimal(10);
                    }

                    lstProfileType.Add(profileType);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstProfileType;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Get List ProfileType By WorkflowId
        /// </summary>
        /// <param name="workflowId">Param workflowId</param>
        /// <returns>Return List ProfileType</returns>
        public IList<ProfileTypeEN> GetProfileTypeByWorkflowId(int workflowId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<ProfileTypeEN> lstProfileType = new List<ProfileTypeEN>();
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileTypeGetByWorkflowId, workflowId);
                while (reader.Read())
                {
                    ProfileTypeEN profileType = new ProfileTypeEN();

                    if (!reader.IsDBNull(0))
                    {
                        profileType.ProfileTypeId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        profileType.WorkflowId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        profileType.DepartmentId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        profileType.ProfileTypeGroupId = reader.GetInt32(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        profileType.ImplementationListId = reader.GetInt32(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        profileType.ResultsImplementationListId = reader.GetInt32(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        profileType.Code = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        profileType.Name = reader.GetString(7);
                    }

                    if (!reader.IsDBNull(8))
                    {
                        profileType.AmountProfile = reader.GetInt32(8);
                    }

                    if (!reader.IsDBNull(9))
                    {
                        profileType.ResolveDays = reader.GetInt32(9);
                    }

                    if (!reader.IsDBNull(10))
                    {
                        profileType.Fees = reader.GetDecimal(10);
                    }

                    lstProfileType.Add(profileType);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstProfileType;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Get List ProfileType By workflowId
        /// </summary>
        /// <param name="workflowid">Param workflowid</param>
        /// <returns>Return List ProfileTypeEN</returns>
        public IList<ProfileTypeEN> GetProfileTypeByStatus(int workflowid)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<ProfileTypeEN> lstProfileType = new List<ProfileTypeEN>();
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VONE_ProfileTypeGetBystatus, workflowid);
                while (reader.Read())
                {
                    ProfileTypeEN profileType = new ProfileTypeEN();

                    if (!reader.IsDBNull(0))
                    {
                        profileType.ProfileTypeId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        profileType.WorkflowId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        profileType.DepartmentId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        profileType.ProfileTypeGroupId = reader.GetInt32(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        profileType.ImplementationListId = reader.GetInt32(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        profileType.ResultsImplementationListId = reader.GetInt32(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        profileType.Code = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        profileType.Name = reader.GetString(7);
                    }

                    if (!reader.IsDBNull(8))
                    {
                        profileType.AmountProfile = reader.GetInt32(8);
                    }

                    if (!reader.IsDBNull(9))
                    {
                        profileType.ResolveDays = reader.GetInt32(9);
                    }

                    if (!reader.IsDBNull(10))
                    {
                        profileType.Fees = reader.GetDecimal(10);
                    }

                    lstProfileType.Add(profileType);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstProfileType;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Exist code
        /// </summary>
        /// <param name="profileTypeId">Param profileTypeId</param>
        /// <param name="code">Param code</param>
        /// <returns>Return Exist</returns>
        public bool Exist(int profileTypeId, string code)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[] 
                { 
                    new SqlParameter(Constants.SqlParamerterName.ProfileType.ProfileTypeId, profileTypeId),
                    new SqlParameter(Constants.SqlParamerterName.ProfileType.Code, code)
                };

                var result = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileTypeExist, sqlParameters));
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return result > 0;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Is used profiletype
        /// </summary>
        /// <param name="profileTypeId">param profiletype</param>
        /// <returns>return true/false</returns>
        public bool IsUsed(int profileTypeId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[] 
                { 
                    new SqlParameter(Constants.SqlParamerterName.ProfileType.ProfileTypeId, profileTypeId),
                };

                var result = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileTypeIsUsed, sqlParameters));
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return result > 0;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Get list ProfileType
        /// </summary>
        /// <param name="SearchProfileTypeNP">Param searchProfileTypeNP</param>
        /// <returns>return list ProfileType</returns>
        public IList<ProfileTypeEN> GetProfileTypeSearch(SearchProfileTypeNP searchProfileTypeNP)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<ProfileTypeEN> lstProfileType = new List<ProfileTypeEN>();

                var code = searchProfileTypeNP.Code;
                var name = searchProfileTypeNP.Name;
                var sqlParameters = new[] 
                { 
                    new SqlParameter(Constants.SqlParamerterName.ProfileType.Code, !string.IsNullOrWhiteSpace(code) ? code.Trim() : (object)DBNull.Value),
                    new SqlParameter(Constants.SqlParamerterName.ProfileType.Name, !string.IsNullOrWhiteSpace(name) ? name.Trim() : (object)DBNull.Value)
                };

                SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneGetProfileTypeSearch, sqlParameters);
                while (reader.Read())
                {
                    ProfileTypeEN profileType = new ProfileTypeEN();

                    if (!reader.IsDBNull(0))
                    {
                        profileType.ProfileTypeId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        profileType.WorkflowId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        profileType.DepartmentId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        profileType.ProfileTypeGroupId = reader.GetInt32(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        profileType.ImplementationListId = reader.GetInt32(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        profileType.ResultsImplementationListId = reader.GetInt32(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        profileType.Code = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        profileType.Name = reader.GetString(7);
                    }

                    if (!reader.IsDBNull(8))
                    {
                        profileType.AmountProfile = reader.GetInt32(8);
                    }

                    if (!reader.IsDBNull(9))
                    {
                        profileType.ResolveDays = reader.GetInt32(9);
                    }

                    if (!reader.IsDBNull(10))
                    {
                        profileType.Fees = reader.GetDecimal(10);
                    }

                    lstProfileType.Add(profileType);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstProfileType;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Get list ProfileType
        /// </summary>
        /// <param name="searchProfileTypeNP">Param searchProfileTypeNP</param>
        /// <returns>return list ProfileType</returns>
        public IList<ProfileTypeEN> GetProfileTypeSearchByNodeWorkflowRollId(SearchProfileTypeNP searchProfileTypeNP)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<ProfileTypeEN> lstProfileType = new List<ProfileTypeEN>();

                var workflowId = searchProfileTypeNP.WorkflowId.HasValue
                               ? searchProfileTypeNP.WorkflowId
                               : (object) DBNull.Value;

                var nodeWorkflowRollId = searchProfileTypeNP.NodeWorkflowRollId.HasValue
                               ? searchProfileTypeNP.NodeWorkflowRollId
                               : (object)DBNull.Value;

                var code = !string.IsNullOrWhiteSpace(searchProfileTypeNP.Code) ? searchProfileTypeNP.Code.Trim() : (object)DBNull.Value;
                var name = !string.IsNullOrWhiteSpace(searchProfileTypeNP.Name) ? searchProfileTypeNP.Name.Trim() : (object)DBNull.Value;

                var sqlParameters = new[] 
                { 
                    new SqlParameter(Constants.SqlParamerterName.ProfileType.WorkflowId, workflowId),
                    new SqlParameter(Constants.SqlParamerterName.ProfileType.NodeWorkflowRollId, nodeWorkflowRollId),
                    new SqlParameter(Constants.SqlParamerterName.ProfileType.Code, code),
                    new SqlParameter(Constants.SqlParamerterName.ProfileType.Name, name)
                };

                SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneGetProfileTypeSearchByNodeWorkflowRollId, sqlParameters);
                while (reader.Read())
                {
                    ProfileTypeEN profileType = new ProfileTypeEN();

                    if (!reader.IsDBNull(0))
                    {
                        profileType.ProfileTypeId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        profileType.WorkflowId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        profileType.DepartmentId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        profileType.ProfileTypeGroupId = reader.GetInt32(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        profileType.ImplementationListId = reader.GetInt32(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        profileType.ResultsImplementationListId = reader.GetInt32(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        profileType.Code = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        profileType.Name = reader.GetString(7);
                    }

                    if (!reader.IsDBNull(8))
                    {
                        profileType.AmountProfile = reader.GetInt32(8);
                    }

                    if (!reader.IsDBNull(9))
                    {
                        profileType.ResolveDays = reader.GetInt32(9);
                    }

                    if (!reader.IsDBNull(10))
                    {
                        profileType.Fees = reader.GetDecimal(10);
                    }
                    
                    lstProfileType.Add(profileType);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstProfileType;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Get list ProfileType
        /// </summary>
        /// <param name="searchProfileTypeNP">Param searchProfileTypeNP</param>
        /// <returns>return list ProfileType</returns>
        public IList<ProfileTypeEN> GetProfileTypeSearchByWorkflow(SearchProfileTypeNP searchProfileTypeNP)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<ProfileTypeEN> lstProfileType = new List<ProfileTypeEN>();

                var workflowId = searchProfileTypeNP.WorkflowId.HasValue
                               ? searchProfileTypeNP.WorkflowId
                               : (object)DBNull.Value;
                var profileTypeGroupId = searchProfileTypeNP.ProfileTypeGroup.HasValue
                               ? searchProfileTypeNP.ProfileTypeGroup
                               : (object)DBNull.Value;

                var code = !string.IsNullOrWhiteSpace(searchProfileTypeNP.Code) ? searchProfileTypeNP.Code.Trim() : (object)DBNull.Value;
                var name = !string.IsNullOrWhiteSpace(searchProfileTypeNP.Name) ? searchProfileTypeNP.Name.Trim() : (object)DBNull.Value;

                var sqlParameters = new[] 
                { 
                    new SqlParameter(Constants.SqlParamerterName.ProfileType.WorkflowId, workflowId),
                    new SqlParameter(Constants.SqlParamerterName.ProfileType.Code, code),
                    new SqlParameter(Constants.SqlParamerterName.ProfileType.Name, name)
                };

                SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneGetProfileTypeSearchByWorkflow, sqlParameters);
                while (reader.Read())
                {
                    ProfileTypeEN profileType = new ProfileTypeEN();

                    if (!reader.IsDBNull(0))
                    {
                        profileType.ProfileTypeId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        profileType.WorkflowId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        profileType.DepartmentId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        profileType.ProfileTypeGroupId = reader.GetInt32(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        profileType.ImplementationListId = reader.GetInt32(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        profileType.ResultsImplementationListId = reader.GetInt32(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        profileType.Code = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        profileType.Name = reader.GetString(7);
                    }

                    if (!reader.IsDBNull(8))
                    {
                        profileType.AmountProfile = reader.GetInt32(8);
                    }

                    if (!reader.IsDBNull(9))
                    {
                        profileType.ResolveDays = reader.GetInt32(9);
                    }

                    if (!reader.IsDBNull(10))
                    {
                        profileType.Fees = reader.GetDecimal(10);
                    }

                    lstProfileType.Add(profileType);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstProfileType;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Gets the profile type search by profile type group.
        /// </summary>
        /// <param name="profileTypeGroupId">The profile type group id.</param>
        /// <returns>return IList<ProfileTypeEN> </returns>
        public IList<ProfileTypeEN> GetProfileTypeSearchByProfileTypeGroup(int profileTypeGroupId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<ProfileTypeEN> lstProfileType = new List<ProfileTypeEN>();
                var sqlParameters = new[] 
                { 
                    new SqlParameter(Constants.SqlParamerterName.ProfileType.ProfileTypeGroupId, profileTypeGroupId)
                };

                SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneGetProfileTypeSearchByProfileTypeGroup, sqlParameters);
                while (reader.Read())
                {
                    ProfileTypeEN profileType = new ProfileTypeEN();

                    if (!reader.IsDBNull(0))
                    {
                        profileType.ProfileTypeId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        profileType.WorkflowId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        profileType.DepartmentId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        profileType.ProfileTypeGroupId = reader.GetInt32(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        profileType.ImplementationListId = reader.GetInt32(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        profileType.ResultsImplementationListId = reader.GetInt32(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        profileType.Code = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        profileType.Name = reader.GetString(7);
                    }

                    if (!reader.IsDBNull(8))
                    {
                        profileType.AmountProfile = reader.GetInt32(8);
                    }

                    if (!reader.IsDBNull(9))
                    {
                        profileType.ResolveDays = reader.GetInt32(9);
                    }

                    if (!reader.IsDBNull(10))
                    {
                        profileType.Fees = reader.GetDecimal(10);
                    }

                    lstProfileType.Add(profileType);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstProfileType;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Gets the profile type search by code.
        /// </summary>
        /// <param name="code">The code.</param>
        /// <returns>return IList<ProfileTypeEN></returns>
        public IList<ProfileTypeEN> GetProfileTypeByCode(string code)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<ProfileTypeEN> lstProfileType = new List<ProfileTypeEN>();
                var sqlParameters = new[] 
                { 
                    new SqlParameter(Constants.SqlParamerterName.ProfileType.Code, code)
                };

                SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneGetProfileTypeSearchByCode, sqlParameters);
                while (reader.Read())
                {
                    ProfileTypeEN profileType = new ProfileTypeEN();

                    if (!reader.IsDBNull(0))
                    {
                        profileType.ProfileTypeId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        profileType.WorkflowId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        profileType.DepartmentId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        profileType.ProfileTypeGroupId = reader.GetInt32(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        profileType.ImplementationListId = reader.GetInt32(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        profileType.ResultsImplementationListId = reader.GetInt32(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        profileType.Code = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        profileType.Name = reader.GetString(7);
                    }

                    if (!reader.IsDBNull(8))
                    {
                        profileType.AmountProfile = reader.GetInt32(8);
                    }

                    if (!reader.IsDBNull(9))
                    {
                        profileType.ResolveDays = reader.GetInt32(9);
                    }

                    if (!reader.IsDBNull(10))
                    {
                        profileType.Fees = reader.GetDecimal(10);
                    }

                    lstProfileType.Add(profileType);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstProfileType;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }
    }
}